﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.Entity;
using SARS.Entity.Factory;

namespace SARS.DAO
{
    public class GroupDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private readonly CourseDAO courseDao = new CourseDAO();
        private readonly UserDAO userDAO = new UserDAO();
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<GroupEntity> RetriveAllGroup()
        {
            var groupList = new List<GroupEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT GroupID, GroupName, CourseID FROM [dbo].[Groups]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    GroupEntity group = GroupFactory.CreateGroup();

                    group.GroupID = Int32.Parse(_dr["GroupID"].ToString());
                    group.GroupName = _dr["GroupName"].ToString();
                    group.CourseID = Int32.Parse(_dr["CourseID"].ToString());
                    groupList.Add(group);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Group] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return groupList;
        }

        public List<GroupEntity> RetriveAllGroupByCourseId(int courseId)
        {
            var groupList = new List<GroupEntity>();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT GroupID, GroupName, CourseID FROM [dbo].[Groups] WHERE [CourseID]=@courseId", _conn);
                _cmd.Parameters.Add("@courseId", SqlDbType.Int);
                _cmd.Parameters["@courseId"].Value = courseId;
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    GroupEntity group = GroupFactory.CreateGroup();
                    group.GroupTutorial = new List<TutorEntity>();

                    group.GroupID = Int32.Parse(_dr["GroupID"].ToString());
                    group.GroupName = _dr["GroupName"].ToString();
                    group.CourseID = Int32.Parse(_dr["CourseID"].ToString());

                    List<TutorEntity> list = userDAO.RetrieveTutorsByGroupId(group.GroupID);
                    if (list != null && list.Count > 0)
                    {
                        group.GroupTutorial = list;
                    }

                    groupList.Add(group);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Group] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return groupList;
        }

        public GroupEntity RetrieveGroupById(int groupId)
        {
            GroupEntity group = null;

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT GroupID, GroupName, CourseID FROM [dbo].[Groups] WHERE [GroupID] = @groupID", _conn);
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (_dr.HasRows && _dr.Read())
                {
                    group = GroupFactory.CreateGroup();

                    group.GroupID = Int32.Parse(_dr["GroupID"].ToString());
                    group.GroupName = _dr["GroupName"].ToString();
                    group.CourseID = Int32.Parse(_dr["CourseID"].ToString());
                    group.Course = courseDao.RetrieveCourseById(group.CourseID);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return group;
        }

        public int CreateGroup(GroupEntity group)
        {
            int newId = 0;
            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "INSERT INTO [dbo].[Groups] ([GroupName],[CourseID]) VALUES (@groupName,@courseID); SELECT CAST(scope_identity() AS int)",
                        _conn);
                _cmd.Parameters.Add("@groupName", SqlDbType.NVarChar);
                _cmd.Parameters["@groupName"].Value = group.GroupName;
                _cmd.Parameters.Add("@courseID", SqlDbType.Int);
                _cmd.Parameters["@courseID"].Value = group.CourseID;

                newId = (int) _cmd.ExecuteScalar();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }
            return newId;
        }

        public bool UpdateGroup(GroupEntity group)
        {
            bool flag = false;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand
                {
                    CommandText = "UPDATE [dbo].[Groups] SET [GroupName] = @groupName WHERE [GroupID] = @groupID"
                };
                _cmd.Parameters.Add("@groupName", SqlDbType.NVarChar);
                _cmd.Parameters["@groupName"].Value = group.GroupName;
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = group.GroupID;
                _cmd.Connection = _conn;

                flag = _cmd.ExecuteNonQuery() >= 1;

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return flag;
        }

        public void RemoveGroup(int groupId)
        {
            SqlTransaction trans = null;
            try
            {
                _conn.Open();
                trans = _conn.BeginTransaction("DeleteGroup");

                _cmd = new SqlCommand("DELETE FROM [StudentGroup] WHERE [GroupID] = @groupId", _conn, trans);
                _cmd.Parameters.Add(new SqlParameter("@groupId", groupId));
                _cmd.ExecuteNonQuery();

                _cmd.CommandText = "DELETE FROM [GroupManager] WHERE [GroupID] = @groupId";
                _cmd.ExecuteNonQuery();

                _cmd.CommandText = "DELETE FROM [Groups] WHERE [GroupID] = @groupId";
                _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                trans.Commit();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
                try
                {
                    if (trans != null) trans.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred 
                    // on the server that would cause the rollback to fail, such as 
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    Console.WriteLine("  Message: {0}", ex2.Message);
                }
            }
            finally
            {
                _conn.Close();
            }
        }

        public void AssignGroupTutor(int groupId, int managerId)
        {
            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "INSERT INTO [dbo].[GroupManager] ([ManagerID],[GroupID]) VALUES (@managerID,@groupID)", _conn);
                _cmd.Parameters.Add("@managerID", SqlDbType.Int);
                _cmd.Parameters["@managerID"].Value = managerId;
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;

                _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }
        }

        public void RemoveGroupTutor(int groupId, int managerId)
        {
            try
            {
                _conn.Open();
                _cmd = new SqlCommand("DELETE FROM [GroupManager] WHERE [GroupID]=@groupId AND [ManagerID]=@managerId",
                    _conn);
                _cmd.Parameters.Add(new SqlParameter("@groupId", groupId));
                _cmd.Parameters.Add(new SqlParameter("@managerId", managerId));

                _cmd.ExecuteNonQuery();

                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }
        }

        public void AssignStudentsIntoGroup(int groupId, int coordinatorId, List<string> listStudentId)
        {
            try
            {
                _conn.Open();
                foreach (string studentId in listStudentId)
                {
                    _cmd =
                        new SqlCommand(
                            "INSERT INTO [dbo].[StudentGroup] ([StudentID],[GroupID],[CoordinatorID],[CreatedDate]) " +
                            "VALUES (@StudentID,@GroupID,@CoordinatorID, GETDATE())", _conn);
                    _cmd.Parameters.Add("@StudentID", SqlDbType.Int);
                    _cmd.Parameters["@StudentID"].Value = Int32.Parse(studentId);
                    _cmd.Parameters.Add("@GroupID", SqlDbType.Int);
                    _cmd.Parameters["@GroupID"].Value = groupId;
                    _cmd.Parameters.Add("@CoordinatorID", SqlDbType.Int);
                    _cmd.Parameters["@CoordinatorID"].Value = coordinatorId;

                    _cmd.ExecuteNonQuery();
                }
                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }
        }

        public void RemoveStudentGroup(int groupId, int studentId)
        {
            //
            try
            {
                _conn.Open();
                _cmd = new SqlCommand("DELETE FROM [StudentGroup] WHERE StudentID = @studentId AND GroupID = @groupId",
                    _conn);
                _cmd.Parameters.Add(new SqlParameter("@groupId", groupId));
                _cmd.Parameters.Add(new SqlParameter("@studentId", studentId));

                _cmd.ExecuteNonQuery();

                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                Trace.WriteLine("[Groups] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }
        }
    }
}